# encoding=utf-8
import ast
import asyncio
import hashlib
import json
import subprocess
import time
import os
import smtplib
from datetime import datetime
import uuid
import aiomysql
import aioredis
from passlib.hash import bcrypt
from loguru import logger
from concurrent.futures import ThreadPoolExecutor
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders
from email.header import Header
from sql import MySQLHelper
from config import DOMAIN, alluredir, WebSocketUrl
from wechat_dingding_bot_api import DingdingRobot, WeixinRobot

# 配置日志
logger.remove()
# 添加文件处理器，设置日志文件路径
logger.add("log/app.log", level="INFO", rotation="10 MB", retention="30 days", compression="zip")

db = MySQLHelper()
domain = DOMAIN


# 登录
async def login_(username, password):
    sql = "select * from member where user=%s;"
    data = await  db.execute_query(sql, username)
    if data:
        if bcrypt.verify(password, data[0]['pwd']):
            hashed_password = bcrypt.using().hash(password)
            sql = "update member set token = %s where id = %s;"
            await db.execute_update(sql, (hashed_password, data[0]['id']))
            sel_sql = "select * from member where user=%s;"
            data = await  db.execute_query(sel_sql, username)
            return data[0]
    return False


# 注册
async def register_(username, password, email):
    sql = "select user from member where user=%s;"
    user = await  db.execute_query(sql, username)
    if user:
        return False
    else:
        try:
            sql = "insert into member(user,pwd,email) values(%s,%s,%s);"
            pwd_hash = bcrypt.using().hash(password)
            await db.execute_update(sql, (username, pwd_hash, email))
            return '注册成功'
        except aiomysql.MySQLError as e:
            return '邮箱已存在'


# 重置密码检查邮箱是否存在
async def check_email_(email):
    sql = "select id,pwd from member where email=%s;"
    pwd = await  db.execute_query(sql, email)
    if pwd:
        return pwd[0]


# 通过邮件链接重置密码
async def email_reset_password_(member_id, token, new_pwd):
    sel_sql = "select pwd from member where id=%s;"
    pwd = await  db.execute_query(sel_sql, member_id)
    if not pwd:
        return False
    else:
        if pwd[0]['pwd'] == token:
            update_sql = "update member set pwd=%s where id=%s;"
            hashed_password = bcrypt.using().hash(new_pwd)
            await db.execute_update(update_sql, (hashed_password, member_id))
            return True
        else:
            print('token不一致')
            return False


# 重置密码
async def reset_password_(member_id, old_pwd, new_pwd):
    sel_sql = "select pwd from member where id=%s;"
    pwd = await  db.execute_query(sel_sql, member_id)
    if not pwd:
        return False
    else:
        if bcrypt.verify(old_pwd, pwd[0]['pwd']):
            update_sql = "update member set pwd=%s where id=%s;"
            hashed_password = bcrypt.using().hash(new_pwd)
            await db.execute_update(update_sql, (hashed_password, member_id))
            return True
        else:
            return False


def generate_sign(timestamp):
    # 拼接字符串：秘钥和时间戳
    sign_str = f"34ft52wed?`e32e{timestamp}"
    # 生成 MD5 签名
    return hashlib.md5(sign_str.encode()).hexdigest()


# 验证sign
def verify_sign_(request_sign, request_time, allowed_time_diff=30):
    current_time = int(time.time())
    # 校验时间戳是否在允许的时间差范围内
    if abs(current_time - int(request_time)) > allowed_time_diff:
        logger.info(
            f"当前时间戳: {current_time}, 请求时间戳: {int(request_time)}, 时间差：{abs(current_time - int(request_time))}")
        return False
    # 使用请求时间戳重新生成签名
    calculated_sign = generate_sign(request_time)
    # 比较客户端的签名和服务端生成的签名
    if calculated_sign == request_sign:
        return True
    else:
        logger.info(f"客户端的签名和服务端签名不一致, 服务端签名: {calculated_sign}, 请求签名: {request_sign}")
        return False


def send_email(url, receiver_email, file_paths=None):  # file_paths传列表
    sender_name = "上官一"  # 发件人名称
    sender_email = "jws6443@163.com"  # 你的发件人邮箱（163邮箱）
    sender_password = "CEGZGOTVOFLMLVMV"  # 你的发件人邮箱密码
    # receiver_email = "249185549@qq.com"  # 收件人邮箱
    # 邮件主题和HTML格式内容
    subject = "重置密码"
    body = """
    <!DOCTYPE html>
    <html>
    <head>
        <meta charset="UTF-8">
        <title>重置您的密码</title>
        <style>
            .container {{
                font-family: Arial, sans-serif;
                line-height: 1.6;
                color: #333;
            }}
            .btn {{
                display: inline-block;
                padding: 10px 20px;
                margin: 20px 0;
                color: #fff;
                background-color: #007bff;
                text-decoration: none;
                border-radius: 5px;
                cursor: pointer;
            }}
        </style>
    </head>
    <body>
        <div class="container">
            <p>尊敬的用户:</p>
            <p>我们收到了您的密码重置请求。如果您没有请求重置密码，请忽略此邮件。</p>
            <p>请点击以下按钮以重置您的密码：</p>
            <a href="{url}" class="btn">重置密码</a>
            <p>该链接仅可使用一次，请尽快完成密码重置。如果链接已失效，请再次尝试通过我们的网站请求重置密码。</p>
            <p>如果您有任何问题或需要进一步的帮助，请联系我们的客服支持。</p>
            <p>感谢您的配合！</p>
            <p>此致，<br>上官集团<br>{domain}<br>客服支持邮箱：<a href="mailto:jws6443@163.com">jws6443@163.com</a></p>
        </div>
    </body>
    </html>
    """.format(url=url, domain=domain)

    if file_paths is None:
        file_paths = []

    # 设置SMTP服务器和端口号（163邮箱）
    smtp_server = "smtp.163.com"
    smtp_port = 25  # 或使用465端口（SSL加密），但需要将server.starttls()g去掉

    server = None  # 初始化server变量

    try:
        # 登录SMTP服务器
        server = smtplib.SMTP(smtp_server, smtp_port)
        server.login(sender_email, sender_password)

        # 创建一个包含邮件内容的MIMEMultipart对象
        message = MIMEMultipart()
        message["From"] = str(Header(sender_name, "utf-8")) + " <" + sender_email + ">"
        message["To"] = receiver_email
        message["Subject"] = subject

        # 添加HTML内容
        msg = MIMEText(body, "html", "utf-8")
        message.attach(msg)

        # 添加文件附件
        if file_paths:  # 检查file_paths是否非空
            for file_path in file_paths:
                with open(file_path, "rb") as f:
                    attachment = MIMEBase("application", "octet-stream")
                    attachment.set_payload(f.read())
                    encoders.encode_base64(attachment)
                    file_name = os.path.basename(file_path)
                    attachment.add_header("Content-Disposition", "attachment",
                                          filename=Header(file_name, "utf-8").encode())
                    message.attach(attachment)

        # 发送邮件
        server.sendmail(sender_email, receiver_email, message.as_string())
        print("邮件发送成功！")
    except Exception as e:
        print(f"邮件发送失败：{e}")
    finally:
        # 关闭连接
        if server is not None:
            server.quit()


# 获取所有方法
async def get_all_method_(case_type):
    try:
        if case_type in [1, '1']:
            sql = "select id,name,type from playwright_method where `type` != 4 order by type,id;"
            data = await  db.execute_query(sql)
        elif case_type in [2, '2']:
            sql = "select id,name,type from playwright_method where `type` = 4 or `type` = 2 and `name` != '断言：元素存在' order by `type` desc;"
            data = await  db.execute_query(sql)
        else:
            data = []
        return data
    except Exception as e:
        return {"数据库报错：": e}


# 获取项目列表
async def get_project_(name='', project_id='', page=1, limit=20):
    try:
        if page:
            offset = (int(page) - 1) * limit
        else:
            offset = 0
        if name:
            select_total = 'select count(id) as total from project where name like %s and delete_time is null order by id desc limit %s offset %s;'
            total = await  db.execute_query(select_total, (f'%{name}%', limit, offset))
            sql = "select id,name,type,modified_by,timing,create_time,update_time,remark from project where name like %s and delete_time is null order by id desc ;"
            data = await  db.execute_query(sql, f'%{name}%')
        elif project_id:
            select_total = 'select count(id) as total from project where id=%s and delete_time is null order by id desc ;'
            total = await  db.execute_query(select_total, (project_id,))
            sql = "select id,name,type,modified_by,timing,create_time,update_time,remark from project where id=%s and delete_time is null order by id desc ;"
            data = await  db.execute_query(sql, project_id)
        elif page is None:
            select_total = 'select count(id) as total from project where delete_time is null order by id desc;'
            total = await  db.execute_query(select_total)
            sql = "select id,name,type,modified_by,timing,create_time,update_time,remark from project where delete_time is null order by id desc ;"
            data = await  db.execute_query(sql)
        else:
            select_total = 'select count(id) as total from project where delete_time is null order by id desc limit %s offset %s;'
            total = await  db.execute_query(select_total, (limit, offset))
            sql = "select id,name,type,modified_by,timing,create_time,update_time,remark from project where delete_time is null order by id desc ;"
            data = await  db.execute_query(sql)
        return {'data': data, 'total': total[0]['total']}
    except Exception as e:
        return {"数据库报错：": e}


# 验证权限
async def verify_permissions_(member_id, ):
    sql = "select permissions from member where id=%s;"
    permissions = await  db.execute_query(sql, member_id)
    return permissions[0]['permissions'] == 1


# 新增项目
async def add_project_(name, project_type, modified_by, remark=''):
    try:
        current_time = time.strftime("%Y-%m-%d %H:%M:%S")
        insert_project = "insert into project(name, type, modified_by, create_time, remark) values(%s,%s,%s,%s,%s);"
        data = (name, project_type, modified_by, current_time, remark)
        await db.execute_update(insert_project, data)
        sel_project_id = "select id from project where name=%s and type=%s and modified_by=%s and create_time=%s and remark=%s;"
        project_id = await  db.execute_query(sel_project_id, data)
        print('test_project添加成功', project_id[0]['id'])
        return {"project_id": project_id[0]['id']}
    except Exception as e:
        return {"数据库报错：": e}


# 修改项目
async def update_project_(project_id, name, project_type, modified_by, remark=''):
    try:
        update_time = time.strftime("%Y-%m-%d %H:%M:%S")
        update_project = "update project set name=%s, type=%s, modified_by=%s, update_time=%s, remark=%s where id=%s;"
        data = (name, project_type, modified_by, update_time, remark, project_id)
        await db.execute_update(update_project, data)
        print('test_project修改成功')
        return {"mes": '修改成功'}
    except Exception as e:
        return {"数据库报错：": e}


# 删除项目
async def delete_project_(project_id, modified_by):
    try:
        query_case = "select id from test_case where project_id=%s and delete_time is null;"
        case = await  db.execute_query(query_case, project_id)
        if case:
            return {"mes": '项目下存在用例，无法删除'}
        else:
            delete_time = time.strftime("%Y-%m-%d %H:%M:%S")
            update_project = "update project set delete_time=%s, modified_by=%s where id=%s;"
            data = (delete_time, modified_by, project_id)
            await db.execute_update(update_project, data)
            print('test_project删除成功')
            return {"mes": '删除成功'}
    except Exception as e:
        return {"数据库报错：": e}


# 添加用例
async def add_uicase_(project_id, project_type, domain_name, title, level, modified_by, steps, state, proxy='',
                      cookie='',
                      remark=''):
    try:
        current_time = time.strftime("%Y-%m-%d %H:%M:%S")
        cookies = str(cookie).replace('\n', '').replace('\t', '').replace('\r', '')
        insert_case = "insert into test_case(project_id, project_type, domain, title, level, proxy, cookie, modified_by, steps, state, create_time, remark) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
        data = (
            project_id, project_type, domain_name, title, level, proxy, str(cookies), modified_by, str(steps), 1,
            current_time,
            remark)
        await db.execute_update(insert_case, data)
        print('test_case添加成功')
        sel_case_id = "select id from test_case where project_id=%s and project_type=%s and `domain`=%s and title=%s and level=%s and proxy=%s and cookie=%s and modified_by=%s and steps=%s and state=%s and create_time=%s and remark=%s;"
        case_id = await  db.execute_query(sel_case_id, data)
        print('test_case添加成功,id:', case_id[0]['id'])
    except Exception as e:
        logger.error(f"添加UIcase失败: {e}")
        return {"数据库报错：": e}
    else:
        await db.init_pool()  # 初始化连接池
        conn = None
        try:
            # 获取连接并启动事务
            conn = await db.pool.acquire()
            async with conn.cursor() as cursor:
                await cursor.execute("START TRANSACTION;")
                for i in steps:
                    print(i)
                    insert_case_step = "insert into case_steps(case_id, project_type, steps_id, name, type, value, element_text, page, variable, actual_results, expected_results, api_url, api_headers, api_data, api_file, create_time, remark) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
                    data = (case_id[0]['id'], project_type, i['steps_id'], i['name'], i['type'],
                            i['value'], str(i['element_text']), str(i['page']), i['variable'], i['actual_results'],
                            i['expected_results'], i['api_url'], i['api_headers'], i['api_data'], i['api_file'],
                            current_time, i['remark']
                            )
                    await cursor.execute(insert_case_step, data)
                # 提交事务
                await conn.commit()
                select_case_step = "select id from case_steps where case_id=%s and project_type=%s and steps_id=%s and name=%s and type=%s and `value`=%s and element_text=%s and page=%s and variable=%s and actual_results=%s and expected_results=%s and api_url=%s and api_headers=%s and api_data=%s and api_file=%s and create_time=%s and remark=%s;"
                case_step = await  db.execute_query(select_case_step, data)
                print('case_step添加成功', case_step[0]['id'])
                return {"case_id": case_id[0]['id']}

        except Exception as e:
            logger.error(f"添加UIcase步骤失败: {e}")
            # 如果连接存在且仍然可用，尝试回滚
            if conn and not conn.closed:
                try:
                    await conn.rollback()
                    logger.info("事务已回滚.")
                except Exception as rollback_error:
                    logger.error(f"回滚失败: {rollback_error}")
            else:
                logger.error("无法回滚，连接已关闭或不可用.")
        finally:
            # 确保连接被释放回连接池
            if conn:
                conn.close()
                logger.info("连接已释放.")


# 修改用例
async def update_uicase_(case_id, domain_name, title, level, modified_by, steps, proxy='', cookie='', remark=''):
    await db.init_pool()  # 初始化连接池
    conn = None
    try:
        # 获取连接并启动事务
        conn = await db.pool.acquire()
        async with conn.cursor() as cursor:
            await cursor.execute("START TRANSACTION;")
            update_time = time.strftime("%Y-%m-%d %H:%M:%S")
            cookies = str(cookie).replace('\n', '').replace('\t', '').replace('\r', '')
            update_case = "update test_case set domain=%s, title=%s, level=%s, proxy=%s, cookie=%s, modified_by=%s, steps=%s, update_time=%s, remark=%s where id=%s;"
            data = (
                domain_name, title, level, proxy, str(cookies), modified_by, str(steps), update_time, remark, case_id)
            await cursor.execute(update_case, data)
            print('test_case修改成功')
            print('步骤', steps)
            query_case_step = "select id from case_steps where case_id=%s and delete_time is null;"
            case_step = await  db.execute_query(query_case_step, case_id)
            steps_id = []
            for i in case_step:
                steps_id.append(i['id'])
            print('case_step', steps_id)
            for i in steps:
                print(i)
                if 'id' not in i:
                    print('id不存在')

                    insert_case_step = "insert into case_steps(case_id, steps_id, name, type, value, element_text, page, variable, actual_results, expected_results, api_url, api_headers, api_data, api_file, create_time, remark) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
                    data = (case_id, i['steps_id'], i['name'], i['type'], i['value'],
                            str(i['element_text']), str(i['page']), i['variable'], i['actual_results'],
                            i['expected_results'],
                            i['api_url'], i['api_headers'], i['api_data'], i['api_file'], update_time, i['remark'])
                    await cursor.execute(insert_case_step, data)
                    print('case_step添加成功')
                else:
                    update_case_step = "update case_steps set steps_id=%s, name=%s, type=%s, value=%s, element_text=%s, page=%s, variable=%s, actual_results=%s, expected_results=%s, api_url=%s, api_headers=%s, api_data=%s, api_file=%s, update_time=%s, remark=%s where id=%s;"
                    data = (i['steps_id'], i['name'], i['type'], i['value'], str(i['element_text']),
                            str(i['page']), i['variable'], i['actual_results'], i['expected_results'],
                            i['api_url'], i['api_headers'], i['api_data'], i['api_file'],
                            update_time, i['remark'], i['id'])
                    await cursor.execute(update_case_step, data)
                    print('case_step修改成功')
            # 提交事务
            await conn.commit()
            return {"mes": '修改成功'}
    except  Exception as e:
        logger.error(f"修改UICase报错: {e}")
        if conn and not conn.closed:
            try:
                await conn.rollback()
                logger.info("事务已回滚.")
            except Exception as rollback_error:
                logger.error(f"回滚失败: {rollback_error}")
        else:
            logger.error("无法回滚，连接已关闭或不可用.")
        return {"数据库报错：": e}
    finally:
        # 确保连接被释放回连接池
        if conn:
            conn.close()
            logger.info("连接已释放.")


# 修改case状态
async def update_uicase_state_(id, state, modified_by):
    try:
        update_time = time.strftime("%Y-%m-%d %H:%M:%S")
        update_case_state = "update test_case set state=%s, modified_by=%s, update_time=%s where id=%s;"
        data = (state, modified_by, update_time, id)
        await db.execute_update(update_case_state, data)
        print('test_case状态修改成功')
        return {"mes": '修改成功'}
    except Exception as e:
        return {"数据库报错：": e}


# 修改用例步骤
async def update_uicase_step_(case_id, modified_by, step_id, name, type, value, page='', element_text='', remark=''):
    try:
        update_case_modified_by = "update test_case set modified_by = %s where id =%s;"
        await db.execute_update(update_case_modified_by, (modified_by, case_id))
        update_time = time.strftime("%Y-%m-%d %H:%M:%S")
        update_case_step = "update case_steps set name=%s, type=%s, value=%s, page=%s, element_text=%s, update_time=%s, remark=%s where id=%s;"
        data = (name, type, value, page, str(element_text), update_time, remark, step_id)
        await db.execute_update(update_case_step, data)
        print('case_step修改成功')
        return {"mes": '修改成功'}
    except Exception as e:
        return {"数据库报错：": e}


# 删除用例
async def delete_uicase_(case_id, modified_by):
    try:
        delete_time = time.strftime("%Y-%m-%d %H:%M:%S")
        delete_case = "update test_case set delete_time=%s, modified_by =%s where id=%s;"
        data = (delete_time, modified_by, case_id)
        await db.execute_update(delete_case, data)
        case_steps = "update case_steps set delete_time=%s where case_id=%s;"
        data = (delete_time, case_id)
        await db.execute_update(case_steps, data)
        return {"mes": '删除成功'}
    except Exception as e:
        return {"数据库报错：": e}


# 删除case步骤
async def delete_uicase_step_(case_id, modified_by, step_id):
    try:
        update_case_modified_by = "update test_case set modified_by = %s where id =%s;"
        await db.execute_update(update_case_modified_by, (modified_by, case_id))
        delete_time = time.strftime("%Y-%m-%d %H:%M:%S")
        delete_case_step = "update case_steps set delete_time=%s where id=%s;"
        data = (delete_time, step_id)
        await db.execute_update(delete_case_step, data)
        return {"mes": '删除成功'}
    except Exception as e:
        return {"数据库报错：": e}


# 查询项目ui用例
async def get_project_uicase_(project_id, case_id='', title='', page=1, limit=20):
    try:
        if case_id:
            query_case = "select id,project_id,title,`domain`,`level`,cookie,proxy,state,modified_by,create_time,update_time,remark from test_case where project_id=%s and id=%s and delete_time is null;"
            data = (project_id, case_id)
            result = await  db.execute_query(query_case, data)
            query_case_steps = "select id,case_id,steps_id,`name`,type,`value`,element_text,page,variable,actual_results,expected_results,api_url,api_headers,api_data,api_file,remark from case_steps where case_id=%s and delete_time is null ORDER BY steps_id;"
            case_steps_result = await  db.execute_query(query_case_steps, case_id)
            result[0]['steps'] = case_steps_result
            return {"data": result}
        # 计算偏移量
        offset = (int(page) - 1) * limit
        if not title:
            select_total = 'select count(id) as total from test_case where project_id=%s and delete_time is null;'
            total = await  db.execute_query(select_total, project_id)
            query_case = "select id,project_id,project_type,title,level,state,modified_by,create_time,update_time from test_case where project_id=%s and delete_time is null order by id desc limit %s offset %s;"
            data = (project_id, limit, offset)
            result = await  db.execute_query(query_case, data)
            return {"data": result, 'total': total[0]['total']}
        else:
            select_total = 'select count(id) as total from test_case where project_id=%s and title like %s and delete_time is null ;'
            total = await  db.execute_query(select_total, (project_id, f'%{title}%'))
            query_case = "select id,project_id,project_type,title,level,state,modified_by,create_time,update_time from test_case where project_id=%s and title like %s and delete_time is null order by id desc limit %s offset %s;"
            data = (project_id, f'%{title}%', limit, offset)
            result = await  db.execute_query(query_case, data)
            return {"data": result, 'total': total[0]['total']}
    except Exception as e:
        return {"数据库报错：": e}


# 复制uicase
async def copy_uicase_(case_id, project_id, modified_by):
    try:
        current_time = time.strftime("%Y-%m-%d %H:%M:%S")
        query_case = "select project_id,project_type,title,`domain`,level,cookie,proxy,steps,state,remark from test_case where project_id=%s and id=%s;"
        case_data = await  db.execute_query(query_case, (project_id, case_id))
        title = f"复制-{case_data[0]['title']}-{int(time.time())}"
        insert_case = "insert into test_case(project_id,project_type,title,`domain`,level,cookie,proxy,modified_by,steps,state,create_time,remark) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
        await db.execute_update(insert_case, (
            case_data[0]['project_id'], case_data[0]['project_type'], title, case_data[0]['domain'],
            case_data[0]['level'], case_data[0]['cookie'], case_data[0]['proxy'], modified_by, case_data[0]['steps'],
            case_data[0]['state'],
            current_time, case_data[0]['remark']))
        query_case_id = "select id from test_case where project_id=%s and title=%s;"
        new_case_id = await  db.execute_query(query_case_id, (project_id, title))
        print('test_case复制成功，id:', new_case_id[0]['id'])
        query_steps = "select project_type,steps_id,name,`state`,type,`value`,element_text,page,variable,actual_results,expected_results,api_url,api_headers,api_data,api_file,remark from case_steps where case_id=%s and delete_time is null order by steps_id;"
        case_steps = await  db.execute_query(query_steps, (case_id,))
    except Exception as e:
        logger.error(f"复制UICase报错: {e}")
        return {"数据库报错：": e}
    else:
        await db.init_pool()  # 初始化连接池
        conn = None
        try:
            # 获取连接并启动事务
            conn = await db.pool.acquire()
            async with conn.cursor() as cursor:
                await cursor.execute("START TRANSACTION;")
                for step in case_steps:
                    insert_case = "insert into case_steps(case_id,project_type,steps_id,name,state,type,`value`,element_text,page,variable,actual_results,expected_results,api_url,api_headers,api_data,api_file,create_time,remark) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
                    await cursor.execute(insert_case, (
                        new_case_id[0]['id'], step['project_type'], step['steps_id'], step['name'], step['state'],
                        step['type'], step['value'], step['element_text'], step['page'],
                        step['variable'], step['actual_results'], step['expected_results'], step['api_url'],
                        step['api_headers'], step['api_data'], step['api_file'],
                        current_time, step['remark']))
                    print('step复制成功')
                # 提交事务
                await conn.commit()
                return {"case_id": new_case_id[0]['id']}
        except Exception as e:
            logger.error(f"复制UIcase步骤失败: {e}")
            # 如果连接存在且仍然可用，尝试回滚
            if conn and not conn.closed:
                try:
                    await conn.rollback()
                    logger.info("事务已回滚.")
                except Exception as rollback_error:
                    logger.error(f"回滚失败: {rollback_error}")
            else:
                logger.error("无法回滚，连接已关闭或不可用.")
            return {"数据库报错：": e}
        finally:
            # 确保连接被释放回连接池
            if conn:
                conn.close()
                logger.info("连接已释放.")


# 添加apiCase
async def add_apicase_(project_id, project_type, domain_name, title, level, modified_by, steps, state, proxy='',
                       headers='',
                       remark=''):
    try:
        current_time = time.strftime("%Y-%m-%d %H:%M:%S")
        insert_case = "insert into test_case(project_id, project_type, domain, title, level, proxy, cookie, modified_by, steps, state, create_time, remark) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
        data = (
            project_id, project_type, domain_name, title, level, proxy, str(headers), modified_by, str(steps), 1,
            current_time,
            remark)
        await db.execute_update(insert_case, data)
        print('test_case添加成功')
        sel_case_id = "select id from test_case where project_id=%s and project_type=%s and `domain`=%s  and title=%s and level=%s and proxy=%s and cookie=%s and modified_by=%s and steps=%s and state=%s and create_time=%s and remark=%s;"
        case_id = await  db.execute_query(sel_case_id, data)
        print('test_case添加成功,id:', case_id[0]['id'])
    except Exception as e:
        logger.error(f"添加APIcase报错: {e}")
        return {"数据库报错：": e}
    else:
        await db.init_pool()  # 初始化连接池
        conn = None
        try:
            # 获取连接并启动事务
            conn = await db.pool.acquire()
            async with conn.cursor() as cursor:
                await cursor.execute("START TRANSACTION;")
                for i in steps:
                    print(i)
                    insert_case_step = "insert into api_case_steps(case_id, project_type, steps_id, name, state, type, path, content_type, data, file, value, actual_results, expected_results, create_time, remark) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
                    data = (case_id[0]['id'], project_type, i['steps_id'], i['name'], state, i['type'],
                            i['path'], str(i['content_type']), str(i['data']), str(i['file']), i['value'],
                            i['actual_results'], i['expected_results'], current_time, i['remark'])
                    await cursor.execute(insert_case_step, data)
                # 提交事务
                await conn.commit()
                select_case_step = "select id from api_case_steps where case_id=%s and project_type=%s and steps_id=%s and `name`=%s and state=%s and type=%s and path=%s and content_type=%s and data=%s and file=%s and `value`=%s and actual_results=%s and expected_results=%s and create_time=%s and remark=%s;"
                case_step = await  db.execute_query(select_case_step, data)
                print('case_step添加成功', case_step[0]['id'])
                return {"case_id": case_id[0]['id']}
        except Exception as e:
            logger.error(f"添加APIcase步骤报错: {e}")

            # 如果连接存在且仍然可用，尝试回滚
            if conn and not conn.closed:
                try:
                    await conn.rollback()
                    logger.info("事务已回滚.")
                except Exception as rollback_error:
                    logger.error(f"回滚失败: {rollback_error}")
            else:
                logger.error("无法回滚，连接已关闭或不可用.")
            return {"数据库报错：": e}
        finally:
            # 确保连接被释放回连接池
            if conn:
                conn.close()
                logger.info("连接已释放.")


# 修改api用例
async def update_apicase_(case_id, domain_name, title, level, modified_by, steps, proxy='', headers='', remark=''):
    await db.init_pool()  # 初始化连接池
    conn = None
    try:
        # 获取连接并启动事务
        conn = await db.pool.acquire()
        async with conn.cursor() as cursor:
            await cursor.execute("START TRANSACTION;")
            update_time = time.strftime("%Y-%m-%d %H:%M:%S")
            update_case = "update test_case set domain=%s, title=%s, level=%s, proxy=%s, cookie=%s, modified_by=%s, steps=%s, update_time=%s, remark=%s where id=%s;"
            data = (
                domain_name, title, level, proxy, str(headers), modified_by, str(steps), update_time, remark, case_id)
            await cursor.execute(update_case, data)
            print('test_case修改成功')
            print('步骤', steps)
            query_case_step = "select id from api_case_steps where case_id=%s and delete_time is null;"
            case_step = await  db.execute_query(query_case_step, case_id)
            steps_id = []
            for i in case_step:
                steps_id.append(i['id'])
            print('case_step', steps_id)
            for i in steps:
                print(i)
                if 'id' not in i:
                    print('id不存在')
                    insert_case_step = "insert into api_case_steps(case_id, steps_id, name, type, path, content_type, data, file, value, actual_results, expected_results, create_time, remark) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
                    data = (case_id, i['steps_id'], i['name'], i['type'],
                            i['path'], str(i['content_type']), str(i['data']), str(i['file']), i['value'],
                            i['actual_results'], i['expected_results'], update_time, i['remark'])
                    await cursor.execute(insert_case_step, data)
                    print('case_step添加成功')
                else:
                    update_case_step = "update api_case_steps set steps_id=%s, name=%s, type=%s, path=%s, content_type=%s, data=%s, file=%s, value=%s, expected_results=%s, actual_results=%s, update_time=%s, remark=%s where id=%s;"
                    data = (
                        i['steps_id'], i['name'], i['type'], i['path'], i['content_type'], i['data'], i['file'],
                        i['value'],
                        i['expected_results'], i['actual_results'],
                        update_time, i['remark'], i['id'])
                    await cursor.execute(update_case_step, data)
                    print('case_step修改成功')
            # 提交事务
            await conn.commit()
        return {"mes": '修改成功'}
    except Exception as e:
        logger.error(f"修改APIcase失败: {e}")
        # 如果连接存在且仍然可用，尝试回滚
        if conn and not conn.closed:
            try:
                await conn.rollback()
                logger.info("事务已回滚.")
            except Exception as rollback_error:
                logger.error(f"回滚失败: {rollback_error}")
        else:
            logger.error("无法回滚，连接已关闭或不可用.")
        return {"数据库报错：": e}
    finally:
        # 确保连接被释放回连接池
        if conn:
            conn.close()
            logger.info("连接已释放.")


# 删除用例
async def delete_apicase_(case_id, modified_by):
    try:
        delete_time = time.strftime("%Y-%m-%d %H:%M:%S")
        delete_case = "update test_case set delete_time=%s, modified_by=%s where id=%s;"
        data = (delete_time, modified_by, case_id)
        await db.execute_update(delete_case, data)
        case_steps = "update api_case_steps set delete_time=%s where case_id=%s;"
        data = (delete_time, case_id)
        await db.execute_update(case_steps, data)
        return {"mes": '删除成功'}
    except Exception as e:
        return {"数据库报错：": e}


# 删除case步骤
async def delete_apicase_step_(case_id, modified_by, step_id):
    try:
        update_case_modified_by = "update test_case set modified_by = %s where id =%s;"
        await db.execute_update(update_case_modified_by, (modified_by, case_id))
        delete_time = time.strftime("%Y-%m-%d %H:%M:%S")
        delete_case_step = "update api_case_steps set delete_time=%s where id=%s;"
        data = (delete_time, step_id)
        await db.execute_update(delete_case_step, data)
        return {"mes": '删除成功'}
    except Exception as e:
        return {"数据库报错：": e}


# 查询项目api用例
async def get_project_apicase_(project_id, case_id='', title='', page=1, limit=20):
    try:
        if case_id:
            query_case = "select id,project_id,title,`domain`,`level`,cookie,proxy,state,modified_by,create_time,update_time,remark from test_case where project_id=%s and id=%s and delete_time is null;"
            data = (project_id, case_id)
            result = await  db.execute_query(query_case, data)
            query_case_steps = "select id,case_id,steps_id,`name`,type,`path`,content_type,data,file,`value`,expected_results,actual_results,remark from api_case_steps where case_id=%s and delete_time is null ORDER BY steps_id;"
            case_steps_result = await  db.execute_query(query_case_steps, case_id)
            result[0]['steps'] = case_steps_result
            return {"data": result}
        # 计算偏移量
        offset = (int(page) - 1) * limit
        if not title:
            select_total = 'select count(id) as total from test_case where project_id=%s and delete_time is null;'
            total = await  db.execute_query(select_total, project_id)
            query_case = "select id,project_id,project_type,title,level,state,modified_by,create_time,update_time from test_case where project_id=%s and delete_time is null order by id desc limit %s offset %s;"
            data = (project_id, limit, offset)
            result = await  db.execute_query(query_case, data)
            return {"data": result, 'total': total[0]['total']}
        else:
            select_total = 'select count(id) as total from test_case where project_id=%s and title like %s and delete_time is null ;'
            total = await  db.execute_query(select_total, (project_id, f'%{title}%'))
            query_case = "select id,project_id,project_type,title,level,state,modified_by,create_time,update_time from test_case where project_id=%s and title like %s and delete_time is null order by id desc limit %s offset %s;"
            data = (project_id, f'%{title}%', limit, offset)
            result = await  db.execute_query(query_case, data)
            return {"data": result, 'total': total[0]['total']}
    except Exception as e:
        return {"数据库报错：": e}


# 复制apicase
async def copy_apicase_(case_id, project_id, modified_by):
    try:
        current_time = time.strftime("%Y-%m-%d %H:%M:%S")
        query_case = "select project_id,project_type,title,`domain`,level,cookie,proxy,steps,state,remark from test_case where project_id=%s and id=%s;"
        case_data = await  db.execute_query(query_case, (project_id, case_id))
        title = f"复制-{case_data[0]['title']}-{int(time.time())}"
        insert_case = "insert into test_case(project_id,project_type,title,`domain`,level,cookie,proxy,modified_by,steps,state,create_time,remark) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
        await db.execute_update(insert_case, (
            case_data[0]['project_id'], case_data[0]['project_type'], title, case_data[0]['domain'],
            case_data[0]['level'], case_data[0]['cookie'], case_data[0]['proxy'], modified_by, case_data[0]['steps'],
            case_data[0]['state'],
            current_time, case_data[0]['remark']))
        query_case_id = "select id from test_case where project_id=%s and title=%s;"
        new_case_id = await  db.execute_query(query_case_id, (project_id, title))
        print('test_case复制成功，id:', new_case_id[0]['id'])
        query_steps = "select project_type,steps_id,name,`state`,type,`path`,content_type,data,file,`value`,expected_results,actual_results,remark from api_case_steps where case_id=%s order by steps_id;"
        case_steps = await  db.execute_query(query_steps, (case_id,))
    except Exception as e:
        logger.error(f"复制APICase报错: {e}")
        return {"数据库报错：": e}
    else:
        await db.init_pool()  # 初始化连接池
        conn = None
        try:
            # 获取连接并启动事务
            conn = await db.pool.acquire()
            async with conn.cursor() as cursor:
                await cursor.execute("START TRANSACTION;")
                for step in case_steps:
                    insert_case = "insert into api_case_steps(case_id,project_type,steps_id,name,state,type,path,content_type,data,file,`value`,expected_results,actual_results,create_time,remark) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
                    await cursor.execute(insert_case, (
                        new_case_id[0]['id'], step['project_type'], step['steps_id'], step['name'], step['state'],
                        step['type'], step['path'], step['content_type'], step['data'], step['file'],
                        step['value'], step['expected_results'], step['actual_results'],
                        current_time, step['remark']))
                    print('case_steps复制成功')
                # 提交事务
                await conn.commit()
                return {"case_id": new_case_id[0]['id']}
        except Exception as e:
            logger.error(f"复制APIcase步骤报错: {e}")
            # 如果连接存在且仍然可用，尝试回滚
            if conn and not conn.closed:
                try:
                    await conn.rollback()
                    logger.info("事务已回滚.")
                except Exception as rollback_error:
                    logger.error(f"回滚失败: {rollback_error}")
            else:
                logger.error("无法回滚，连接已关闭或不可用.")
            return {"数据库报错：": e}
        finally:
            # 确保连接被释放回连接池
            if conn:
                conn.close()
                logger.info("连接已释放.")


# 添加到测试报告表
async def add_test_report_(case_id, title, project_name, run_environment):
    try:
        create_time = time.strftime("%Y-%m-%d %H:%M:%S")
        add_test_result = "INSERT INTO test_report (case_id, title, project_name, run_environment, create_time) VALUES (%s,%s, %s, %s, %s);"
        data = (str(case_id), str(title), project_name, run_environment, create_time)
        await db.execute_update(add_test_result, data)
        query_test_result = "SELECT id,case_id FROM test_report WHERE case_id = %s AND title=%s AND project_name=%s AND run_environment=%s AND state = 0 AND create_time = %s;"
        test_result_id = await  db.execute_query(query_test_result, data)
        print('添加到测试报告表', test_result_id)
        return {"data": test_result_id}
    except Exception as e:
        print(f"数据库报错：{e}")
        return {"数据库报错：": str(e)}


async def select_case_id(report_id):
    current_time = datetime.now()
    date_str = await  db.execute_query('SELECT id,create_time FROM test_report ORDER BY id DESC LIMIT 1 OFFSET 1;')
    # 将时间字符串转换为 datetime 对象
    logger.info(f"{date_str[0]}")
    create_time = datetime.strptime(date_str[0]['create_time'], '%Y-%m-%d %H:%M:%S')
    time_difference = current_time - create_time
    logger.info(f"时间差：{time_difference}")

    case_id = []
    select = 'select case_id, run_environment from test_report  where state = 0 and id = %s;'
    data = await  db.execute_query(select, report_id)
    logger.info(data)
    if data:
        case = ast.literal_eval(data[0]['case_id'])
        for i in case:
            case_id.append(i)
    result = {
        'case_id': case_id,
        'report_id': report_id,
        'run_environment': data[0]['run_environment']
    }
    return json.dumps(result)


# 执行case
executor = ThreadPoolExecutor(max_workers=10)


async def execute_case(case_data, threads=1):
    """实际执行测试用例的函数"""

    print('运行case', time.strftime("%Y-%m-%d %H:%M:%S"))
    timestamp = time.strftime('%Y%m%d%H%M%S')
    unique_id = uuid.uuid4().hex  # 生成唯一ID
    allure_dir = alluredir(f"{timestamp}_{unique_id}")  # 使用唯一ID生成目录
    os.makedirs(allure_dir, exist_ok=True)
    logger.info(time.strftime("%Y-%m-%d %H:%M:%S"))

    # 选择测试文件
    test_file = './tests/test_01.py' if case_data['run_environment'] in [1, 2] else './tests/test_02.py'

    # 构建pytest命令
    pytest_cmd = [
        'pytest', test_file,
        '-v',
        f'--alluredir={allure_dir}',
        f'--case_ids={case_data["case_id"]}',
        f'--report_id={case_data["report_id"]}',
        '-n', f'{threads}',
        '--dist', 'load',
        # '--reruns', '2'
    ]

    # 执行测试
    if os.name != 'nt':  # 非Windows系统
        case_process = await asyncio.create_subprocess_exec(
            *pytest_cmd,
            stdout=asyncio.subprocess.PIPE,
            stderr=asyncio.subprocess.PIPE
        )
        stdout, stderr = await case_process.communicate()
    else:  # Windows系统
        loop = asyncio.get_event_loop()
        case_process = await loop.run_in_executor(
            executor,
            lambda: subprocess.run(pytest_cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE, text=True,
                                   encoding='utf-8')
        )
        stdout, stderr = case_process.stdout, case_process.stderr

    print('case运行正常输出', stdout)
    # print('case运行异常输出', stderr)

    # 生成报告
    command = f"allure generate {allure_dir} -o {allure_dir}/reports && allure-combine {allure_dir}/reports --dest {allure_dir}/reports"
    if os.name != 'nt':  # 非Windows系统
        report_process = await asyncio.create_subprocess_shell(
            command,
            stdout=asyncio.subprocess.PIPE,
            stderr=asyncio.subprocess.PIPE
        )
        stdout, stderr = await report_process.communicate()
    else:  # Windows系统
        loop = asyncio.get_event_loop()
        report_process = await loop.run_in_executor(
            executor,
            lambda: subprocess.run(command, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE, text=True,
                                   encoding='utf-8')
        )
        stdout, stderr = report_process.stdout, report_process.stderr

    # 生成报告路径
    path = f'{domain}' + allure_dir.replace('/home', '') + '/reports/complete.html#behaviors'
    return case_process.returncode, path, case_data['report_id']


# 更新case状态
async def update_test_status(report_id: int, state: int, path: str):
    """更新测试报告状态"""
    update_sql = "update test_report set state = %s, path = %s where id = %s;"
    await db.execute_update(update_sql, (state, path, report_id))


# 创建后台任务
async def background_run_case(casedata, redis, send_message_to_redis, threads=1):
    """后台运行测试用例"""
    if isinstance(casedata, str):
        case_data = ast.literal_eval(casedata)  # 只有字符串才调用 literal_eval
    else:
        case_data = casedata
    # 先更新状态为运行中
    await update_test_status(case_data['report_id'], 3, '')

    # 执行测试
    exit_code, path, report_id = await execute_case(case_data, threads)

    # redis = await aioredis.from_url('redis://localhost', encoding="utf-8", decode_responses=True)

    # 根据结果更新状态
    if exit_code == 0:
        state = 1
        await send_message_to_redis(redis, str(report_id), {"status": "complete", "message": f"执行成功,报告地址:{path}"})
    else:
        state = 2
        await send_message_to_redis(redis, str(report_id), {"status": "complete", "message": f"执行失败,报告地址:{path}"})
    await update_test_status(report_id, state, path)
    await redis.close()

# 查询测试报告
async def get_test_report_(name='', state='', page=1, limit=20):
    try:
        # 计算偏移量
        offset = (int(page) - 1) * limit

        # 根据不同的条件构建SQL查询语句
        if name and state:
            select_total = 'select count(id) as total from test_report where delete_time is null and title like %s and state = %s;'
            total = await  db.execute_query(select_total, (f'%{name}%', state))
            select = 'select * from test_report where title like %s and state = %s and delete_time is null order by id desc limit %s offset %s;'
            data = await  db.execute_query(select, (f'%{name}%', state, limit, offset))
        elif name:
            select_total = 'select count(id) as total from test_report where delete_time is null and title like %s;'
            total = await  db.execute_query(select_total, (f'%{name}%',))
            select = 'select * from test_report where title like %s and delete_time is null order by id desc limit %s offset %s;'
            data = await  db.execute_query(select, (f'%{name}%', limit, offset))
        elif state:
            select_total = 'select count(id) as total from test_report where delete_time is null and state = %s;'
            total = await  db.execute_query(select_total, (state,))
            select = 'select * from test_report where state = %s and delete_time is null order by id desc limit %s offset %s;'
            data = await  db.execute_query(select, (state, limit, offset))
        else:
            select_total = 'select count(id) as total from test_report where delete_time is null;'
            total = await  db.execute_query(select_total)
            select = 'select * from test_report where delete_time is null order by id desc limit %s offset %s;'
            data = await  db.execute_query(select, (limit, offset))
        return {"data": data, "total": total[0]['total']}
    except Exception as e:
        print(f"数据库报错：{e}")
        return {"数据库报错：": str(e)}


# 删除测试报告
async def delete_test_report_(test_report_id):
    try:
        delete_time = time.strftime("%Y-%m-%d %H:%M:%S")
        delete = "update test_report set delete_time=%s where id=%s;"
        await db.execute_update(delete, (delete_time, test_report_id))
        return {"data": '删除成功'}
    except Exception as e:
        print(f"数据库报错：{e}")
        return {"数据库报错：": str(e)}


# 设置定时任务
async def set_scheduled_tasks_(project_id, modified_by, scheduled='', notice_type='', threads=1, notice_token='',
                               notice_secret=''):
    try:
        query_timing = 'select name,timing from project where `id` != %s and delete_time is null and timing is not null and timing <> "";'
        timing = await  db.execute_query(query_timing, (project_id,))
        for i in timing:
            print(i)
            if scheduled == i['timing']:
                return {"data": [], "mes": f"该时间已被 {i['name']} 使用，请更换"}
        update_time = time.strftime("%Y-%m-%d %H:%M:%S")
        set_scheduled = 'update project set modified_by=%s,timing=%s,notice_type=%s,threads=%s,notice_token=%s,notice_secret=%s,update_time=%s where id = %s;'
        await db.execute_update(set_scheduled,
                                (modified_by, scheduled, notice_type, threads, notice_token, notice_secret, update_time,
                                 project_id))
        select_scheduled_tasks = 'select id,name,type,timing,notice_type,threads,notice_token,notice_secret from project where delete_time is null and id = %s;'
        data = await  db.execute_query(select_scheduled_tasks, (project_id,))
        return {"data": data}
    except Exception as e:
        print(f"数据库报错：{e}")
        return {"数据库报错：": str(e)}


# 查询定时任务
async def get_scheduled_tasks_(project_id):
    try:
        select = 'select id,name,type,timing,notice_type,threads,notice_token,notice_secret from project where delete_time is null and id = %s;'
        data = await  db.execute_query(select, (project_id,))
        return {"data": data}
    except Exception as e:
        print(f"数据库报错：{e}")
        return {"数据库报错：": str(e)}


# 添加账号
async def add_account_(name, type, cookies, modified_by, remark=''):
    try:
        create_time = time.strftime("%Y-%m-%d %H:%M:%S")
        cookies = str(cookies).replace('\n', '').replace('\r', '').replace('\t', '')
        insert = 'insert into account(name,type,cookies,modified_by,remark,create_time) values(%s,%s,%s,%s,%s,%s);'
        await db.execute_update(insert, (name, type, cookies, modified_by, remark, create_time))
        select = "select id,name,type,cookies,modified_by,remark from account where delete_time is null and name=%s;"
        data = await  db.execute_query(select, (name,))
        return {"data": data}
    except Exception as e:
        print(f"数据库报错：{e}")
        return {"数据库报错：": str(e)}


# 修改账号
async def update_account_(id, name, type, cookies, modified_by, remark=''):
    try:
        update_time = time.strftime("%Y-%m-%d %H:%M:%S")
        cookies = str(cookies).replace('\n', '').replace('\r', '').replace('\t', '')
        update = 'update account set name=%s,type=%s,cookies=%s,modified_by=%s,remark=%s,update_time=%s where id=%s;'
        await db.execute_update(update, (name, type, cookies, modified_by, remark, update_time, id))
        select = "select id,name,type,cookies,modified_by,remark from account where delete_time is null and id=%s;"
        data = await  db.execute_query(select, (id,))
        return {"data": data[0], "mes": "修改成功"}
    except Exception as e:
        print(f"数据库报错：{e}")
        return {"数据库报错：": str(e)}


# 查询账号
async def get_account_(name, id="", type="", page=1, limit=20):
    try:
        if id:
            select = "select id,name,type,cookies,modified_by,remark from account where delete_time is null and id=%s;"
            data = await  db.execute_query(select, (id,))
            return {"data": data[0]}
        elif name:
            offset = (int(page) - 1) * limit
            select = "select id,name,type,cookies,modified_by,create_time,update_time,remark from account where delete_time is null and name like %s order by create_time limit %s offset %s;"
            data = await  db.execute_query(select, (f"%{name}%", limit, offset))
            total = await  db.execute_query(
                "select count(*) as total from account where delete_time is null and name=%s;",
                (name,))
            return {"data": data, "total": total[0]['total']}
        elif type:
            select = "select id,name,type,cookies,modified_by,create_time,update_time,remark from account where delete_time is null and type=%s order by create_time desc;"
            data = await  db.execute_query(select, (type,))
            return {"data": data}
        else:
            offset = (int(page) - 1) * limit
            select = "select id,name,type,cookies,modified_by,create_time,update_time,remark from account where delete_time is null order by create_time desc limit %s offset %s;"
            data = await  db.execute_query(select, (limit, offset))
            total = await  db.execute_query("select count(*) as total from account where delete_time is null;")
            return {"data": data, "total": total[0]['total']}
    except Exception as e:
        print(f"数据库报错：{e}")
        return {"数据库报错：": str(e)}


# 删除账号
async def delete_account_(id, modified_by):
    try:
        query = "select cookie from test_case where cookie = %s and delete_time is null;"
        data = await  db.execute_query(query, (id,))
        if data:
            return {"mes": "账号已被使用，无法删除"}
        else:
            delete = "update account set delete_time=%s, modified_by=%s where id=%s;"
            await db.execute_update(delete, (time.strftime("%Y-%m-%d %H:%M:%S"), modified_by, id))
            return {"mes": "删除成功"}
    except Exception as e:
        print(f"数据库报错：{e}")
        return {"数据库报错：": str(e)}


# 复制账号
async def copy_account_(id, modified_by):
    try:
        select = "select name,type,cookies,remark from account where id=%s;"
        data = await  db.execute_query(select, (id,))
        create_time = time.strftime("%Y-%m-%d %H:%M:%S")
        name = f"{data[0]['name']}-{int(time.time())}"
        insert = 'insert into account(name,type,cookies,modified_by,create_time,remark) values(%s,%s,%s,%s,%s,%s);'
        await db.execute_update(insert, (
            name, data[0]['type'], data[0]['cookies'], modified_by, create_time, data[0]['remark']))
        insert = "select id from account where name=%s;"
        query_data = await  db.execute_query(insert, (name,))
        return {"id": query_data[0]["id"], "mes": "复制成功"}
    except Exception as e:
        print(f"数据库报错：{e}")
        return {"数据库报错：": str(e)}


# 上线触发执行case
async def trigger_execute_case_(project_id, threads=1):
    try:
        select = "select p.name, p.type, p.notice_type, p.notice_token, p.notice_secret, t.id from project p, test_case t where p.id = t.project_id and t.delete_time is null and t.state = 1 and p.id = %s;"
        data = await  db.execute_query(select, (project_id,))
        case_id = []
        if not data:
            logger.error('没有可执行的case')
            return {'mes': '没有可执行的case'}
        for i in data:
            case_id.append(i['id'])

        # 添加测试报告
        report_id = await add_test_report_(case_id, '上线触发执行', data[0]['name'], data[0]['type'])

        result = {
            'case_id': case_id,
            'report_id': report_id['data'][0]['id'],
            'run_environment': data[0]['type'],
        }

        if data[0]['notice_type'] == 1:
            DingdingRobot(data[0]['notice_token'], data[0]['notice_secret']).markdown(
                f"{data[0]['name']}上线 case开始执行", f"## {data[0]['name']}上线 case开始执行\n")
        elif data[0]['notice_type'] == 2:
            WeixinRobot(data[0]['notice_token']).markdown_api(
                f"# {data[0]['name']}上线 case开始执行")
        else:
            print('无通知方式')

        # 更新状态为运行中
        await update_test_status(result['report_id'], 3, '')

        # 执行用例
        exit_code, path, report_id = await execute_case(result, threads)
        if exit_code == 0:
            print('执行成功')
            await update_test_status(report_id, 1, path)
            if data[0]['notice_type'] == 1:
                DingdingRobot(data[0]['notice_token'], data[0]['notice_secret']).action_card(
                    f"{data[0]['name']}上线 case执行通过",
                    f"![screenshot](https://gitee.com/shangguanyi666/picture/raw/master/%E8%BF%90%E8%A1%8C%E6%88%90%E5%8A%9F.png) \n\n #### {data[0]['name']} 上线 case执行通过 \n\n 请通过测试报告查看详情",
                    f"{domain}",
                    f"{path}"
                )
            elif data[0]['notice_type'] == 2:
                WeixinRobot(data[0]['notice_token']).img_text_api(
                    f"{data[0]['name']}上线 case执行通过",
                    path,
                    'https://gitee.com/shangguanyi666/picture/raw/master/%E8%BF%90%E8%A1%8C%E5%A4%B1%E8%B4%A5.png',
                    '请通过测试报告查看详情')
            else:
                print('无通知方式')
        else:
            print('执行失败')
            await update_test_status(report_id, 2, path)
            if data[0]['notice_type'] == 1:
                DingdingRobot(data[0]['notice_token'], data[0]['notice_secret']).action_card(
                    f"{data[0]['name']}上线 case执行报错",
                    f"![screenshot](https://gitee.com/shangguanyi666/picture/raw/master/%E8%BF%90%E8%A1%8C%E5%A4%B1%E8%B4%A5.png) \n\n #### {data[0]['name']} 上线 case执行报错 \n\n 请通过测试报告查看详情",
                    f"{domain}",
                    f"{path}"
                )
            elif data[0]['notice_type'] == 2:
                WeixinRobot(data[0]['notice_token']).img_text_api(
                    f"{data[0]['name']}上线 case执行报错",
                    path,
                    'https://gitee.com/shangguanyi666/picture/raw/master/%E8%BF%90%E8%A1%8C%E5%A4%B1%E8%B4%A5.png',
                    '请通过测试报告查看详情')
            else:
                print('无通知方式')
    except Exception as e:
        logger.error(f"上线触发case报错：{e}")
        return {"数据库报错：": str(e)}
